const { pool } = require('../config/db');

class Adoption {
  // Create adoption application
  static async createApplication(applicationData) {
    const {
      user_id,
      pet_id,
      reason_for_adoption,
      living_situation,
      previous_pet_experience,
      additional_notes
    } = applicationData;

    try {
      // Check if user already has a pending application for this pet
      const [existing] = await pool.query(
        'SELECT * FROM adoption_applications WHERE user_id = ? AND pet_id = ? AND status = "pending"',
        [user_id, pet_id]
      );

      if (existing.length > 0) {
        throw new Error('You already have a pending application for this pet');
      }

      const [result] = await pool.query(
        `INSERT INTO adoption_applications 
          (user_id, pet_id, reason_for_adoption, living_situation, previous_pet_experience, additional_notes)
        VALUES (?, ?, ?, ?, ?, ?)`,
        [user_id, pet_id, reason_for_adoption, living_situation, previous_pet_experience || null, additional_notes || null]
      );

      return this.getApplicationById(result.insertId);
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get adoption application by ID
  static async getApplicationById(id) {
    try {
      const [rows] = await pool.query(
        `SELECT aa.*, 
                p.name as pet_name, 
                p.image_url as pet_image,
                u.username as applicant_username,
                u.full_name as applicant_name
         FROM adoption_applications aa
         JOIN pets p ON aa.pet_id = p.id
         JOIN users u ON aa.user_id = u.id
         WHERE aa.id = ?`,
        [id]
      );
      return rows[0];
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get all applications by user ID
  static async getApplicationsByUser(userId) {
    try {
      const [rows] = await pool.query(
        `SELECT aa.*, 
                p.name as pet_name, 
                p.image_url as pet_image
         FROM adoption_applications aa
         JOIN pets p ON aa.pet_id = p.id
         WHERE aa.user_id = ?
         ORDER BY aa.application_date DESC`,
        [userId]
      );
      return rows;
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get all applications for a pet
  static async getApplicationsByPet(petId) {
    try {
      const [rows] = await pool.query(
        `SELECT aa.*, 
                u.username as applicant_username,
                u.full_name as applicant_name,
                u.email as applicant_email,
                u.phone as applicant_phone
         FROM adoption_applications aa
         JOIN users u ON aa.user_id = u.id
         WHERE aa.pet_id = ?
         ORDER BY aa.application_date DESC`,
        [petId]
      );
      return rows;
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get all applications (admin)
  static async getAllApplications(status = null, page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    let query = `
      SELECT aa.*, 
             p.name as pet_name, 
             p.image_url as pet_image,
             u.username as applicant_username,
             u.full_name as applicant_name
      FROM adoption_applications aa
      JOIN pets p ON aa.pet_id = p.id
      JOIN users u ON aa.user_id = u.id
    `;
    
    const queryParams = [];
    
    if (status) {
      query += ' WHERE aa.status = ?';
      queryParams.push(status);
    }
    
    query += ' ORDER BY aa.application_date DESC LIMIT ? OFFSET ?';
    queryParams.push(parseInt(limit), offset);
    
    try {
      const [rows] = await pool.query(query, queryParams);
      
      // Get total count for pagination
      let countQuery = 'SELECT COUNT(*) as total FROM adoption_applications';
      if (status) {
        countQuery += ' WHERE status = ?';
      }
      
      const [countResult] = await pool.query(
        countQuery,
        status ? [status] : []
      );
      
      return {
        applications: rows,
        pagination: {
          total: countResult[0].total,
          page: parseInt(page),
          limit: parseInt(limit),
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Update application status
  static async updateApplicationStatus(id, status, userId = null) {
    try {
      if (status === 'approved') {
        // If approving, update approval_date
        await pool.query(
          'UPDATE adoption_applications SET status = ?, approval_date = NOW() WHERE id = ?',
          [status, id]
        );
        
        // Get pet_id from the application
        const [appRows] = await pool.query(
          'SELECT pet_id FROM adoption_applications WHERE id = ?',
          [id]
        );
        
        if (appRows.length > 0) {
          // Mark pet as adopted
          await pool.query(
            'UPDATE pets SET is_adopted = TRUE WHERE id = ?',
            [appRows[0].pet_id]
          );
          
          // Create adoption record
          if (userId) {
            await pool.query(
              'INSERT INTO adoption_records (application_id, adoption_fee) VALUES (?, 0)',
              [id]
            );
          }
        }
      } else {
        // Just update status
        await pool.query(
          'UPDATE adoption_applications SET status = ? WHERE id = ?',
          [status, id]
        );
      }
      
      return this.getApplicationById(id);
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get adoption records
  static async getAdoptionRecords(page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    
    try {
      const [rows] = await pool.query(
        `SELECT ar.*, 
                aa.user_id, aa.pet_id,
                p.name as pet_name, 
                p.image_url as pet_image,
                u.username as adopter_username,
                u.full_name as adopter_name
         FROM adoption_records ar
         JOIN adoption_applications aa ON ar.application_id = aa.id
         JOIN pets p ON aa.pet_id = p.id
         JOIN users u ON aa.user_id = u.id
         ORDER BY ar.adoption_date DESC
         LIMIT ? OFFSET ?`,
        [parseInt(limit), offset]
      );
      
      // Get total count for pagination
      const [countResult] = await pool.query(
        'SELECT COUNT(*) as total FROM adoption_records'
      );
      
      return {
        records: rows,
        pagination: {
          total: countResult[0].total,
          page: parseInt(page),
          limit: parseInt(limit),
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(error.message);
    }
  }

  // Get user's adopted pets
  static async getUserAdoptions(userId) {
    try {
      const [rows] = await pool.query(
        `SELECT ar.*, 
                p.id as pet_id,
                p.name as pet_name, 
                p.image_url as pet_image,
                p.breed, p.age, p.gender
         FROM adoption_records ar
         JOIN adoption_applications aa ON ar.application_id = aa.id
         JOIN pets p ON aa.pet_id = p.id
         WHERE aa.user_id = ?
         ORDER BY ar.adoption_date DESC`,
        [userId]
      );
      return rows;
    } catch (error) {
      throw new Error(error.message);
    }
  }
}

module.exports = Adoption; 